﻿Imports System.Data.SqlClient
Public Class FrmNhanhoso
    Private Sub FrmNhanhoso_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LoadHuyen(Cmbhuyen)
        'LoadXa(Convert.ToInt32(Cmbhuyen.SelectedValue.ToString), CmbXa)
        DataGridViewNhanhoso.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewNhanhoso.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        DataGridViewBaocao.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewBaocao.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
    End Sub

    Private Sub Cmbhuyen_SelectedIndexChanged(sender As Object, e As EventArgs) Handles Cmbhuyen.SelectedIndexChanged
        LoadXa(Convert.ToInt32(Cmbhuyen.SelectedValue.ToString), CmbXa)
    End Sub

    Private Sub ButtonNhap_Click(sender As Object, e As EventArgs) Handles ButtonNhap.Click
        Dim tennhap As String
        tennhap = ModuleTenDangNhap.m_tennhap
        Dim sql As String
        Dim tb As New DataTable
        Dim NgayNhan As String
        NgayNhan = String.Format("{0:MM/dd/yyyy}", DateTimeNgayNhan.Value)

        sql = " INSERT INTO [NhanThongBao]" &
           "([NgayNhan]" &
           ",[HuyenID]" &
           ",[XaID]" &
           ",[SoLuongCaNhan]" &
           ",[Ngaynhap]" &
           ",[NguoiNhap]" &
           ",[SoluongToChuc])" &
       " VALUES ('" & NgayNhan & "', '" & Cmbhuyen.SelectedValue.ToString & "', " &
       " '" & CmbXa.SelectedValue.ToString & "', '" & TextBoxSoLuongCaNhan.Text & "', " &
       " " & Format(Now.ToOADate() - 2) & ",'" & tennhap & "', '" & TextBoxSoLuongToChuc.Text & "')"
        Dim flag As Boolean
        flag = ThaoTacDuLieu(sql)

        If (flag = True) Then
            MessageBox.Show("Lưu thông tin mới thành công")
            napDuLieuNhapVaodataGird()
        End If
    End Sub
    Public Sub napDuLieuNhapVaodataGird()
        DataGridViewNhanhoso.Rows.Add(DateTimeNgayNhan.Text, Cmbhuyen.Text, CmbXa.Text, TextBoxSoLuongCaNhan.Text, TextBoxSoLuongToChuc.Text)
    End Sub
    Private Sub DataGridViewNhanhoso_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewNhanhoso.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewNhanhoso.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewNhanhoso.RowHeadersWidth < Size.Width + 20 Then DataGridViewNhanhoso.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub

    Private Sub ButtonThoat_Click(sender As Object, e As EventArgs) Handles ButtonThoat.Click
        Me.Close()
    End Sub
    Private Sub TinhTong()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        Dim TuNgay As String
        TuNgay = String.Format("{0:MM/dd/yyyy}", DateTimeTuNgay.Value)
        Dim DenNgay As String
        DenNgay = String.Format("{0:MM/dd/yyyy}", DateTimeDenNgay.Value)
        dbComm = New SqlCommand("select h.T_huyen, min(ngaynhan) as tungay, max(ngaynhan) as denngay, Sum(soluongcanhan) as canhan, sum(soluongtochuc) as tochuc " &
                               " From  NHANTHONGBAO tb, TENHUYEN h  WHERE " &
                               "  datediff (day, ngaynhap, '" & TuNgay & "') <= 0  " &
                               " and datediff (day, ngayNhap, '" & DenNgay & "') >= 0 " &
                               " and tb.HuyenID= h.maH group by h.T_huyen", ob_cnn)

        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewBaocao.DataSource = tbl
        If (tbl.Rows.Count = 0) Then
            MessageBox.Show("Không tìm thấy thông tin !")
        Else
        End If
        ob_cnn.Close()
    End Sub

    Private Sub XuatExcel()
        Dim gr As SaveFileDialog = New SaveFileDialog()
        Dim FileName As String
        With gr
            .InitialDirectory = "D:\"
            .Filter = "Microsoft Excel Worksheet (*.xls)|*.xls"
            .FilterIndex = 1
        End With
        If gr.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            FileName = gr.FileName
        Else
            Exit Sub
        End If
        'Extracting from database
        Dim col, row As Integer
        Dim Excel As Object = CreateObject("Excel.Application")

        If Excel Is Nothing Then
            MsgBox("m***cel re..", MsgBoxStyle.Critical)
            Return
        End If
        'Export to Excel process
        Try
            With Excel
                .SheetsInNewWorkbook = 1
                .Workbooks.Add()
                .Worksheets(1).Select()
                .Worksheets(1).name = "Tiep Nhan"
                .Range("a1:e1").Columns.Borders.Value = 1
                Dim i As Integer = 1
                For col = 0 To DataGridViewBaocao.Columns.Count - 1
                    .cells(1, i).value = DataGridViewBaocao.Columns(col).HeaderText
                    .cells(1, i).EntireRow.Font.Bold = True
                    i += 1
                Next
                i = 2
                Dim k As Integer = 1
                For col = 0 To DataGridViewBaocao.Columns.Count - 1
                    i = 2
                    For row = 0 To DataGridViewBaocao.Rows.Count - 1
                        With .worksheets(1)
                            .Cells(i, k).Value = DataGridViewBaocao.Rows(row).Cells(col).Value

                            i += 1
                        End With
                    Next
                    k += 1
                Next
                .ActiveCell.Worksheet.SaveAs(FileName)
            End With
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
            Excel = Nothing
            MsgBox("Dữ liệu đã được xuất thành công!", MsgBoxStyle.Information)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        ' The excel is created and opened for insert value. We most close this excel using this system
        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
        For Each i As Process In pro
            i.Kill()
        Next
    End Sub
    Private Sub ButtonBaocao_Click(sender As Object, e As EventArgs) Handles ButtonBaocao.Click
        TinhTong()
    End Sub

    Private Sub ButtonXuatExcel_Click(sender As Object, e As EventArgs) Handles ButtonXuatExcel.Click
        XuatExcel()
    End Sub

    Private Sub DataGridViewBaocao_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewBaocao.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewBaocao.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewBaocao.RowHeadersWidth < Size.Width + 20 Then DataGridViewBaocao.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub

    Private Sub DataGridViewBaocao_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewBaocao.CellContentClick

    End Sub
End Class